﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using STOCK.Model;

namespace STOCK.DAL
{
    public class T_FormulaDAL
    {
        public T_Formula ToModel(DataRow row)
        {
            T_Formula item=new  T_Formula();
            item.Id=(Guid)SqlHelper.FromDbValue(row["Id"]);           
            item.CatId=(Guid)SqlHelper.FromDbValue(row["CatId"]);           
            item.Name=((String)SqlHelper.FromDbValue(row["Name"])).ToUpper();           
            item.Formula=((String)SqlHelper.FromDbValue(row["Formula"])).ToUpper();           
            item.Discription=((String)SqlHelper.FromDbValue(row["Discription"])).ToUpper();           
            return item;
        }
        private T_Formula[] ToT_Formulas(DataTable table)
        {
            T_Formula[] items=new T_Formula[table.Rows.Count];
            for (int i=0;i<table.Rows.Count;i++)
            {
                items[i]=ToModel(table.Rows[i]);
            }
            return items;
        }
        
        public T_Formula[] ListAll()
        {
            DataTable table=SqlHelper.ExecuteDataTable("Select * from T_Formula");
            return ToT_Formulas(table);
        }
        public T_Formula[] Search(string sql,List<SqlParameter> parameters)
        {
            DataTable table = SqlHelper.ExecuteDataTable(sql,parameters.ToArray());
            return ToT_Formulas(table);
        }
        public void Update(T_Formula item)
        {
            SqlHelper.ExecuteNonQuery(@"Update T_Formula set
			CatId=@CatId
			,Name=@Name
			,Formula=@Formula
			,Discription=@Discription
                    where Id=@Id"
			,new SqlParameter("@Id",item.Id)
			,new SqlParameter("@CatId",item.CatId)
			,new SqlParameter("@Name",item.Name)
			,new SqlParameter("@Formula",item.Formula)
			,new SqlParameter("@Discription",item.Discription));            
        }
        public void Insert(T_Formula item)
        {
             SqlHelper.ExecuteNonQuery(@"INSERT INTO T_Formula
                (Id,CatId,Name,Formula,Discription)
                Values(newid(),@CatId,@Name,@Formula,@Discription)" 			
			,new SqlParameter("@CatId",item.CatId)
			,new SqlParameter("@Name",item.Name)
			,new SqlParameter("@Formula",item.Formula)
			,new SqlParameter("@Discription",item.Discription));
        }
        public T_Formula GetById(Guid id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Formula where Id=@Id",
                new SqlParameter("@Id",id));
            if (table.Rows.Count <= 0)
            {
                return null;
            }
            else if (table.Rows.Count == 1)
            {
                return ToModel(table.Rows[0]);
            }
            else
            {
                throw new Exception();
            }
        }
        public void DeleteById(Guid id)
        {
            SqlHelper.ExecuteNonQuery("Delete from T_Formula where Id=@Id",
                new SqlParameter("@Id", id));
            
        }
      
    }
}
